/*
 *	(C) 2001 Granitar, Inc  (cd)
 *
 *	This procedure evaluates the state of files listed in table filelist:
 *
 *	create table filelist
 *	(
 *		status	  char(10),
 *		asset	   char(64),
 *		filename	varchar(255)
 *	);
 *
 *	The script tries to find the file in any of the asset tables. Depending on the result, it
 *	sets the status attribute in the filelist table to the following possible values:
 *
 *	Status	Description						Action				Precedence
 *	-----------------------------------------------------------------------------------------------------------
 *	GOOD	One asset found with a status other than VOID		keep the file			2
 *	VOID	One asset found with a status of 'VOID'			delete the file			1
 *	MVOID	More than one asset found, more than one not 'VOID'	delete the file			1
 *	MULTI	More than one asset found, more than one are not void	keep the file, check assets	2
 *	ORPH	no corresponding asset found				check assets			3 
 */

create or replace procedure p_filestat as
begin
	declare
		i	integer;
		cursor	file_cursor is
			select filename from filelist;
	begin
	
		-- initialize filelist tables
		update filelist set status = 'ORPH', asset = '';
	
		-- loop through list of files and compare filename with asset tables
		for file_val in file_cursor
		loop
		-- amArticle
			select count(*) into i from amarticle 
			where 
				filename = file_val.filename
				and status = 'VO';
			if i=1
			then
				update filelist set status = 'VOID', asset = 'amArticle' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MVOID', asset = 'amArticle' where upper(filename) = upper(file_val.filename);
			end if;

		
			select count(*) into i from amarticle 
			where 
				filename = file_val.filename
				and status <> 'VO';
			if i=1
			then
				update filelist set status = 'GOOD', asset = 'amArticle' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MULTI', asset = 'amArticle' where upper(filename) = upper(file_val.filename);
			end if;
			
			
			
		-- amCopy
			select count(*) into i from amcopy
			where 
				filename = file_val.filename
				and status = 'VO';
			if i=1
			then
				update filelist set status = 'VOID', asset = 'amCopy' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MVOID', asset = 'amCopy' where upper(filename) = upper(file_val.filename);
			end if;

		
			select count(*) into i from amcopy
			where 
				filename = file_val.filename
				and status <> 'VO';
			if i=1
			then
				update filelist set status = 'GOOD', asset = 'amCopy' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MULTI', asset = 'amCopy' where upper(filename) = upper(file_val.filename);
			end if;
			
			
			
		-- amDestination
			select count(*) into i from amDestination
			where 
				filename = file_val.filename
				and status = 'VO';
			if i=1
			then
				update filelist set status = 'VOID', asset = 'amDestination' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MVOID', asset = 'amDestination' where upper(filename) = upper(file_val.filename);
			end if;

		
			select count(*) into i from amDestination
			where 
				filename = file_val.filename
				and status <> 'VO';
			if i=1
			then
				update filelist set status = 'GOOD', asset = 'amDestination' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MULTI', asset = 'amDestination' where upper(filename) = upper(file_val.filename);
			end if;
			
			
			
		-- amPromotion
			select count(*) into i from amPromotion
			where 
				filename = file_val.filename
				and status = 'VO';
			if i=1
			then
				update filelist set status = 'VOID', asset = 'amPromotion' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MVOID', asset = 'amPromotion' where upper(filename) = upper(file_val.filename);
			end if;

		
			select count(*) into i from amPromotion
			where 
				filename = file_val.filename
				and status <> 'VO';
			if i=1
			then
				update filelist set status = 'GOOD', asset = 'amPromotion' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MULTI', asset = 'amPromotion' where upper(filename) = upper(file_val.filename);
			end if;
			
			
			
		-- amStation
			select count(*) into i from amStation
			where 
				filename = file_val.filename
				and status = 'VO';
			if i=1
			then
				update filelist set status = 'VOID', asset = 'amStation' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MVOID', asset = 'amStation' where upper(filename) = upper(file_val.filename);
			end if;

		
			select count(*) into i from amStation
			where 
				filename = file_val.filename
				and status <> 'VO';
			if i=1
			then
				update filelist set status = 'GOOD', asset = 'amStation' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MULTI', asset = 'amStation' where upper(filename) = upper(file_val.filename);
			end if;



		-- amTest
			select count(*) into i from amTest
			where 
				filename = file_val.filename
				and status = 'VO';
			if i=1
			then
				update filelist set status = 'VOID', asset = 'amTest' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MVOID', asset = 'amTest' where upper(filename) = upper(file_val.filename);
			end if;

		
			select count(*) into i from amTest
			where 
				filename = file_val.filename
				and status <> 'VO';
			if i=1
			then
				update filelist set status = 'GOOD', asset = 'amTest' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MULTI', asset = 'amTest' where upper(filename) = upper(file_val.filename);
			end if;



		-- amTrain
			select count(*) into i from amTrain
			where 
				filename = file_val.filename
				and status = 'VO';
			if i=1
			then
				update filelist set status = 'VOID', asset = 'amTrain' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MVOID', asset = 'amTrain' where upper(filename) = upper(file_val.filename);
			end if;

		
			select count(*) into i from amTrain
			where 
				filename = file_val.filename
				and status <> 'VO';
			if i=1
			then
				update filelist set status = 'GOOD', asset = 'amTrain' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MULTI', asset = 'amTrain' where upper(filename) = upper(file_val.filename);
			end if;




		-- Article
			select count(*) into i from Article
			where 
				filename = file_val.filename
				and status = 'VO';
			if i=1
			then
				update filelist set status = 'VOID', asset = 'Article' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MVOID', asset = 'Article' where upper(filename) = upper(file_val.filename);
			end if;


			select count(*) into i from Article
			where 
				filename = file_val.filename
				and status <> 'VO';
			if i=1
			then
				update filelist set status = 'GOOD', asset = 'Article' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MULTI', asset = 'Article' where upper(filename) = upper(file_val.filename);
			end if;
		


		-- Collection
			select count(*) into i from Collection
			where 
				filename = file_val.filename
				and status = 'VO';
			if i=1
			then
				update filelist set status = 'VOID', asset = 'Collection' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MVOID', asset = 'Collection' where upper(filename) = upper(file_val.filename);
			end if;

		
			select count(*) into i from Collection
			where 
				filename = file_val.filename
				and status <> 'VO';
			if i=1
			then
				update filelist set status = 'GOOD', asset = 'Collection' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MULTI', asset = 'Collection' where upper(filename) = upper(file_val.filename);
			end if;




		-- Image
			select count(*) into i from Image
			where 
				filename = file_val.filename
				and status = 'VO';
			if i=1
			then
				update filelist set status = 'VOID', asset = 'Image' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MVOID', asset = 'Image' where upper(filename) = upper(file_val.filename);
			end if;

		
			select count(*) into i from Image
			where 
				filename = file_val.filename
				and status <> 'VO';
			if i=1
			then
				update filelist set status = 'GOOD', asset = 'Image' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MULTI', asset = 'Image' where upper(filename) = upper(file_val.filename);
			end if;




		-- Linkset 
			select count(*) into i from Linkset
			where 
				filename = file_val.filename
				and status = 'VO';
			if i=1
			then
				update filelist set status = 'VOID', asset = 'Linkset' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MVOID', asset = 'Linkset' where upper(filename) = upper(file_val.filename);
			end if;

		
			select count(*) into i from Linkset
			where 
				filename = file_val.filename
				and status <> 'VO';
			if i=1
			then
				update filelist set status = 'GOOD', asset = 'Linkset' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MULTI', asset = 'Linkset' where upper(filename) = upper(file_val.filename);
			end if;



		-- Page
			select count(*) into i from Page
			where 
				filename = file_val.filename
				and status = 'VO';
			if i=1
			then
				update filelist set status = 'VOID', asset = 'Page' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MVOID', asset = 'Page' where upper(filename) = upper(file_val.filename);
			end if;

		
			select count(*) into i from Page
			where 
				filename = file_val.filename
				and status <> 'VO';
			if i=1
			then
				update filelist set status = 'GOOD', asset = 'Page' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MULTI', asset = 'Page' where upper(filename) = upper(file_val.filename);
			end if;



		-- Template
			select count(*) into i from Template
			where 
				filename = file_val.filename
				and status = 'VO';
			if i=1
			then
				update filelist set status = 'VOID', asset = 'Template' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MVOID', asset = 'Template' where upper(filename) = upper(file_val.filename);
			end if;

		
			select count(*) into i from Template
			where 
				filename = file_val.filename
				and status <> 'VO';
			if i=1
			then
				update filelist set status = 'GOOD', asset = 'Template' where upper(filename) = upper(file_val.filename);
			end if;
			
			if i>1
			then
				update filelist set status = 'MULTI', asset = 'Template' where upper(filename) = upper(file_val.filename);
			end if;


		end loop;
	end;
end;

/